package com.hc.comm.Service;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.hc.bean.GradeBean;

import test.Student;

public class CreateSimpleExcelToDisk {

	public final static String[] rawWebExcelHead = { "ID", "测试网址", "平均时延(ms)","最大时延","最小时延","丢包率","抖动",
		"测试次数","用户帐号","内网IP"
			,"外网IP", "VlanID1", "VlanID2", "时间" };
	
	public final static String[] rawDownFileExcelHead = { "ID", "平均速度","最大速度","最小速度","用户帐号","内网IP"
		,"外网IP", "VlanID1", "VlanID2", "时间"};
	public final static String[] rawCompanyExcelHead = { "排名", "网页测试得分", "时延得分","抖动得分","丢包得分","网页测试次数",
		"下载测试得分", "下载次数", "公司", "时间" };
	public final static String[] rawVlanFileExcelHead = { "排名", "网页测试得分", "时延得分","抖动得分","丢包得分","网页测试次数",
		"下载测试得分", "下载次数", "地区", "时间" };
	
	
	public static void createRawExcel(List data, String fileName, String dataType) {
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet("原始数据");
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row = sheet.createRow((short) 0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		HSSFCell cell = null;

		if ("网页测试".equals(dataType)) {

			for (int i = 0; i < rawWebExcelHead.length; i++) {

				cell = row.createCell((short) i);
				cell.setCellValue(rawWebExcelHead[i]);
				cell.setCellStyle(style);
			}
			for (int i = 0; i < data.size(); i++) {
				row = sheet.createRow((int) i + 1);
				Map map = (Map) data.get(i);
				// 第四步，创建单元格，并设置值
				row.createCell((short) 0).setCellValue(
						Double.parseDouble(map.get("indexe").toString()));
				row.createCell((short) 1).setCellValue(map.get("url").toString());
				row.createCell((short) 2).setCellValue(
						Double.parseDouble(map.get("timedelayAvr").toString()));
				row.createCell((short) 3)
						.setCellValue(
								Double.parseDouble(map.get("timedelayMax").toString()));
				row.createCell((short) 4).setCellValue(
						Double.parseDouble(map.get("timedelayMin").toString()));
				row.createCell((short) 5).setCellValue(
						Double.parseDouble(map.get("lostRate").toString()));
				row.createCell((short) 6).setCellValue(
						Double.parseDouble(map.get("shake").toString()));
				row.createCell((short) 7).setCellValue(
						Double.parseDouble(map.get("testCount").toString()));
				row.createCell((short) 8).setCellValue(map.get("userNumber").toString());
				row.createCell((short) 9).setCellValue(map.get("intranetIp").toString());
				row.createCell((short) 10).setCellValue(map.get("externalIp").toString());
				row.createCell((short) 11).setCellValue(map.get("vlanId1").toString());
				row.createCell((short) 12).setCellValue(map.get("vlanId2").toString());
				row.createCell((short) 13).setCellValue(
						map.get("cmdTime").toString());
			}

		} else {

			for (int i = 0; i < rawDownFileExcelHead.length; i++) {

				cell = row.createCell((short) i);
				cell.setCellValue(rawDownFileExcelHead[i]);
				cell.setCellStyle(style);
			}
			for (int i = 0; i < data.size(); i++) {
				row = sheet.createRow((int) i + 1);
				Map map = (Map) data.get(i);
				// 第四步，创建单元格，并设置值
				row.createCell((short) 0).setCellValue(
						Double.parseDouble(map.get("indexe").toString()));
	
				row.createCell((short) 1).setCellValue(
						Double.parseDouble(map.get("speedAvr").toString()));
				row.createCell((short) 2)
						.setCellValue(
								Double.parseDouble(map.get("speedMax").toString()));
				row.createCell((short) 3).setCellValue(
						Double.parseDouble(map.get("speedMin").toString()));
				row.createCell((short) 4).setCellValue(map.get("userNumber").toString());
				row.createCell((short) 5).setCellValue(map.get("intranetIp").toString());
				row.createCell((short) 6).setCellValue(map.get("externalIp").toString());
				row.createCell((short) 7).setCellValue(map.get("vlanId1").toString());
				row.createCell((short) 8).setCellValue(map.get("vlanId2").toString());
				row.createCell((short) 9).setCellValue(
						map.get("cmdTime").toString());
			}

		}
		// 第六步，将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream("E:/" + fileName
					+ ".xls");
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public static void createGradeExcel(List<GradeBean> data, String fileName, String datType) {
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet=null;
		if ("公司".equals(datType)) {
			sheet = wb.createSheet("公司得分排名");
		}else {
			sheet = wb.createSheet("Vlan得分排名");
			
		}
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row = sheet.createRow((short) 0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		HSSFCell cell = null;

		if ("公司".equals(datType)) {

			for (int i = 0; i < rawCompanyExcelHead.length; i++) {

				cell = row.createCell((short) i);
				cell.setCellValue(rawCompanyExcelHead[i]);
				cell.setCellStyle(style);
			}


		} else {

			for (int i = 0; i < rawVlanFileExcelHead.length; i++) {

				cell = row.createCell((short) i);
				cell.setCellValue(rawVlanFileExcelHead[i]);
				cell.setCellStyle(style);
			}


		}
		
		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow((int) i + 1);
			GradeBean bean = data.get(i);
			// 第四步，创建单元格，并设置值
		
			if(i==0){
				if ("公司".equals(datType)) {
					
					row.createCell((short) 0).setCellValue("-");
				}else{
					
					row.createCell((short) 0).setCellValue((i+1)+"");
				}
				
			}else{
				
				if ("公司".equals(datType)) {
					
					row.createCell((short) 0).setCellValue(i);
				}else{
					
					row.createCell((short) 0).setCellValue((i+1)+"");
				}
				
			}
//			 "排名", "网页测试得分", "时延得分","抖动得分","丢包得分","网页测试次数",
//				"下载测试得分", "下载次数", "区域", "时间" 
			
			row.createCell((short) 1).setCellValue(bean.getWebGrade()+"");
			row.createCell((short) 2).setCellValue(bean.getWebtimedelayGrade()+"");
			row.createCell((short) 3).setCellValue(bean.getWebShakeGrade()+"");
			row.createCell((short) 4).setCellValue(bean.getWeblostRateGrade()+"");
			row.createCell((short) 5).setCellValue(bean.getWebTestCount()+"");
			row.createCell((short) 6).setCellValue(bean.getDownFileGrade()+"");
			row.createCell((short) 7).setCellValue(bean.getDownFileTestCount()+"");
			row.createCell((short) 8).setCellValue(bean.getVlanId());
			row.createCell((short) 9).setCellValue(bean.getTime());
		}
		// 第六步，将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream("E:/" + fileName
					+ ".xls");
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
}
